package com.iris.live.services.report.sources;

import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by lichao on 2017/8/29.
 */
@Component("AfterMarketBusinessDataSource")
public class AfterMarketBusinessDataSource  extends BaseReportDataSource {
    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info=context.getParams();
        String lineType=info.get("displayType").toString();
        List<Map> datas =new ArrayList<>();
        if("1".equals(lineType)){
            datas=  getTimeLineData(info);
        }
        else if("2".equals(lineType)){
            datas=  getOrgLineData(info);
        }else{
            datas = getModelLineData(info);
        }

        super.brandGroup(context ,datas);
        return  datas;
    }

    private List<Map> getTimeLineData(Map info){
        String sql = "select   " +
                "             产品种类,  " +
                "             concat(采购来源,'_',产品种类)采购来源,  " +
                "             concat(采购来源,'_产品总计') 产品总计,   " +
                "             采购成本,  " +
                "             营业额,  " +
                "             利润,  " +
                "             date,  " +
                "             成本调整,  " +
                "             品牌  " +
                "          from lsh经销商表  allset  " +
                "          join(  " +
                "                select   " +
                "                a.permit,  " +
                "                e.产品种类,  " +
                "                if(c.采购来源 = 'RMB','集采','自采')采购来源,  " +
                "                sum(b.成本*b.数量*a.type) 采购成本,  " +
                "                sum(b.折后价*b.数量*a.type) 营业额,  " +
                "                sum((b.折后价-b.成本)*b.数量*a.type) 利润,  " +
                "                a.date,  " +
                "                0 成本调整,  " +
                "                a.品牌  " +
                "           from   " +
                "               (  " +
                "                   select  a.permit, b.品牌, a.boutique_id 精品,  " +
                "                   CASE   " +
                "                   WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay   AND     " +
                "                   a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN 0   " +
                "                   WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay  THEN 1    " +
                "                   WHEN a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN -1    " +
                "                   END TYPE,  " +
                "                   DATE_FORMAT(a.invoice_date,'%m')date  " +
                "                from   " +
                "                t_retail_base_info a, LSH车型 b  " +
                "                where   " +
                "                   a.car_model_id=b.`车型编号`  " +
                "                   AND   " +
                "                   a.boutique_id is not null AND   ((a.invoice_date <= :endDay  " +
                "                   AND a.invoice_date >=:startDay )  " +
                "                   OR (a.refund_date <= :endDay   AND a.refund_date >=:startDay ))   " +
                "                   and 1=1   " +
                "               )a  " +
                "           LEFT JOIN 报价精品详细表 b   " +
                "           ON a.精品 = b.编号  " +
                "           JOIN lsh精品信息 c  " +
                "           ON b.id = c.精品编号   " +
                "           LEFT JOIN lsh精品分类 d  " +
                "           ON c.分类编号 = d.分类编号  " +
                "           LEFT JOIN lsh精品种类 e  " +
                "           ON d.种类编号 = e.种类编号  " +
                "       GROUP BY   " +
                "           品牌,产品种类 ,采购来源, date " +
                "       UNION all  " +
                "       select   " +
                "               permit,  " +
                "               '' 产品种类,  " +
                "               '' 采购来源 ,  " +
                "               0 采购成本,  " +
                "               0 营业额,  " +
                "               0 利润,  " +
                "               DATE_FORMAT(valid_start_date,'%m') date,  " +
                "               sum(after_market_product_cost_buy_adjust) 成本调整,  " +
                "               b.品牌  " +
                "        FROM  " +
                "           INCOME_ADJUSTMENT a,  " +
                "           lsh库存表 c,  " +
                "           lsh车型 b  " +
                "       where a.type = 1  " +
                "       and a.STOCK_CODE = c.库存编号  " +
                "       and b.车型编号 = c.车型编号  " +
                "       and DATE_FORMAT(valid_start_date,'%Y') = :year   " +
                "       and DATE_FORMAT(valid_start_date,'%m')>=DATE_FORMAT(:startDay,'%m')  " +
                "       and DATE_FORMAT(valid_start_date,'%m')<=DATE_FORMAT(:endDay,'%m')  " +
                "       and 1=1  " +
                "   GROUP BY DATE_FORMAT(valid_start_date,'%m')  " +
                "   )a  " +
                "   ON a.permit=allset.许可_BMBS  " +
                "   where 2=2  ";
        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql = sql.replace("2=2", " allset.大区= :permit  or  allset.大区_EN=:permit or " +
                    "   allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit");
            map.put("permit", permit);
        }

        String selBrand = info.get("selBrand") == null ? "" : info.get("selBrand").toString();
        if(StringUtils.isNotNullOrBlank(selBrand) && !"all".equals(selBrand) && !"全部".equals(selBrand)){
            conditionSql.append(" and b.品牌 = :model ");
            map.put("model",selBrand);
        }

        String selMotorcycleType = info.get("selMotorcycleType") == null ? "" : info.get("selMotorcycleType").toString();
        if(StringUtils.isNotNullOrBlank(selMotorcycleType) && !"all".equals(selMotorcycleType) && !"全部".equals(selMotorcycleType)){//车系
            conditionSql.append(" and b.级别 = :MotorcycleType ");
            map.put("MotorcycleType",selMotorcycleType);
        }

        String selMotorcycleModel = info.get("selMotorcycleModel") == null ? "" : info.get("selMotorcycleModel").toString();
        if(StringUtils.isNotNullOrBlank(selMotorcycleModel) && !"all".equals(selMotorcycleModel) && !"全部".equals(selMotorcycleModel)){//车型
            conditionSql.append(" and b.车型 = :MotorcycleModel ");
            map.put("MotorcycleModel",selMotorcycleModel);
        }

        sql = sql.replace("1=1",conditionSql);
        map.put("startDay",info.get("startDay"));
        map.put("endDay", info.get("endDay"));
        map.put("year",info.get("selectYear"));
        return this.getList(sql,map);
    }

    private List<Map> getOrgLineData(Map info){
        String sql = "select    'DCH' 集团,allset.许可_LSH,allset.大区 AS 大区, allset.大区_EN AS 大区_EN,  " +
                "               IF ( isnull(allset.CTA) OR allset.CTA = '', '其他cta', allset.CTA ) AS CTA,   " +
                "               IF (isnull(allset.CTA_EN) OR allset.CTA_EN = '', 'Other CTA', allset.CTA_EN ) AS CTA_EN,  " +
                "               allset.许可_BMBS AS 许可_BMBS,  " +
                "               allset.经销商名 AS 经销商名,  " +
                "               allset.经销商名_EN AS 经销商名_EN,  " +
                "               allset.SalesCode AS salesCode,  " +
                "               产品种类,  " +
                "               concat(采购来源,'_',产品种类)采购来源,  " +
                "               concat(采购来源,'_产品总计') 产品总计,   " +
                "               采购成本,  " +
                "               营业额,  " +
                "               利润,  " +
                "               成本调整,  " +
                "               品牌  " +
                "   from lsh经销商表  allset  " +
                "   join(  " +
                "        select   " +
                "               a.permit,  " +
                "               e.产品种类,  " +
                "               if(c.采购来源 = 'RMB','集采','自采')采购来源,  " +
                "               sum(b.成本*b.数量*a.type) 采购成本,  " +
                "               sum(b.折后价*b.数量*a.type) 营业额,  " +
                "               sum((b.折后价-b.成本)*b.数量*a.type) 利润,  " +
                "               a.date,  " +
                "               0 成本调整,  " +
                "               a.品牌  " +
                "        from   " +
                "        (      " +
                "               select  a.permit, b.品牌, a.boutique_id 精品,  " +
                "                       CASE   " +
                "                       WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay   AND     " +
                "                       a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN 0   " +
                "                       WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay  THEN 1    " +
                "                       WHEN a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN -1    " +
                "                       END TYPE,  " +
                "                       DATE_FORMAT(a.invoice_date,'%m')date  " +
                "                from   " +
                "                t_retail_base_info a, LSH车型 b  " +
                "                where   " +
                "                       a.car_model_id=b.`车型编号`  " +
                "                       AND   " +
                "                       a.boutique_id is not null AND   ((a.invoice_date <= :endDay  " +
                "                       AND a.invoice_date >=:startDay )  " +
                "                       OR (a.refund_date <= :endDay   AND a.refund_date >=:startDay ))   " +
                "                       and 1=1   " +
                "        )a  " +
                "        LEFT JOIN 报价精品详细表 b   " +
                "        ON a.精品 = b.编号  " +
                "        JOIN lsh精品信息 c  " +
                "        ON b.id = c.精品编号   " +
                "        LEFT JOIN lsh精品分类 d  " +
                "        ON c.分类编号 = d.分类编号  " +
                "        LEFT JOIN lsh精品种类 e  " +
                "        ON d.种类编号 = e.种类编号  " +
                "   GROUP BY   " +
                "       permit,品牌,产品种类 ,采购来源, date " +
                "   UNION all  " +
                "       select   " +
                "           permit,  " +
                "           '' 产品种类,  " +
                "           '' 采购来源 ,  " +
                "           0 采购成本,  " +
                "           0 营业额,  " +
                "           0 利润,  " +
                "           DATE_FORMAT(valid_start_date,'%m') date,  " +
                "           sum(after_market_product_cost_buy_adjust) 成本调整,  " +
                "           b.品牌  " +
                "       FROM  " +
                "       INCOME_ADJUSTMENT a,  " +
                "       lsh库存表 c,  " +
                "       lsh车型 b  " +
                "       where   a.type = 1  " +
                "               and a.STOCK_CODE = c.库存编号  " +
                "               and b.车型编号 = c.车型编号  " +
                "               and DATE_FORMAT(valid_start_date,'%Y') = :year   " +
                "               and DATE_FORMAT(valid_start_date,'%m')>=DATE_FORMAT(:startDay,'%m')  " +
                "               and DATE_FORMAT(valid_start_date,'%m')<=DATE_FORMAT(:endDay,'%m')  " +
                "               and 1=1  " +
                "       GROUP BY permit ,DATE_FORMAT(valid_start_date,'%m')  " +
                "   )a  " +
                "   ON a.permit=allset.许可_BMBS  ";

        Map map = Maps.newHashMap();
        StringBuffer conditionSql = new StringBuffer("1=1");

        String selBrand = info.get("selBrand") == null ? "" : info.get("selBrand").toString();
        if(StringUtils.isNotNullOrBlank(selBrand) && !"all".equals(selBrand) && !"全部".equals(selBrand)){
            conditionSql.append(" and b.品牌 = :model ");
            map.put("model",selBrand);
        }

        String selMotorcycleType = info.get("selMotorcycleType") == null ? "" : info.get("selMotorcycleType").toString();
        if(StringUtils.isNotNullOrBlank(selMotorcycleType) && !"all".equals(selMotorcycleType) && !"全部".equals(selMotorcycleType)){//车系
            conditionSql.append(" and b.级别 = :MotorcycleType ");
            map.put("MotorcycleType",selMotorcycleType);
        }

        String selMotorcycleModel = info.get("selMotorcycleModel") == null ? "" : info.get("selMotorcycleModel").toString();
        if(StringUtils.isNotNullOrBlank(selMotorcycleModel) && !"all".equals(selMotorcycleModel) && !"全部".equals(selMotorcycleModel)){//车型
            conditionSql.append(" and b.车型 = :MotorcycleModel ");
            map.put("MotorcycleModel",selMotorcycleModel);
        }

        sql = sql.replace("1=1",conditionSql);
        map.put("startDay",info.get("startDay"));
        map.put("endDay",info.get("endDay"));
        map.put("year",info.get("selectYear"));

        if(!"LSH".equals(info.get("permit"))){
            sql+=" where allset.大区= :permit  or  allset.大区_EN=:permit or    allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit  ";
            map.put("permit", info.get("permit"));
        }
        return this.getList(sql,map);
    }

    private List<Map> getModelLineData(Map info){
        String sql = "select   " +
                "               产品种类,  " +
                "               concat(采购来源,'_',产品种类)采购来源,  " +
                "               concat(采购来源,'_产品总计') 产品总计,   " +
                "               采购成本,  " +
                "               营业额,  " +
                "               利润,  " +
                "               date,  " +
                "               成本调整,  " +
                "               品牌  " +
                "   from lsh经销商表  allset  " +
                "   join(  " +
                "        select   " +
                "               a.permit,  " +
                "               e.产品种类,  " +
                "               if(c.采购来源 = 'RMB','集采','自采')采购来源,  " +
                "               sum(b.成本*b.数量*a.type) 采购成本,  " +
                "               sum(b.折后价*b.数量*a.type) 营业额,  " +
                "               sum((b.折后价-b.成本)*b.数量*a.type) 利润,  " +
                "               a.date,  " +
                "               0 成本调整,  " +
                "               a.品牌  " +
                "        from   " +
                "        (      " +
                "                select a.permit, b.品牌, a.boutique_id 精品,  " +
                "                       CASE   " +
                "                       WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay   AND     " +
                "                       a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN 0   " +
                "                       WHEN a.invoice_date >=:startDay AND a.invoice_date <= :endDay  THEN 1    " +
                "                       WHEN a.refund_date >=:startDay AND a.refund_date <= :endDay  THEN -1    " +
                "                       END TYPE,  " +
                "                       DATE_FORMAT(a.invoice_date,'%m')date  " +
                "                from   " +
                "                t_retail_base_info a, LSH车型 b  " +
                "                where   " +
                "                       a.car_model_id=b.`车型编号`  " +
                "                       AND   " +
                "                       a.boutique_id is not null AND   ((a.invoice_date <= :endDay  " +
                "                       AND a.invoice_date >=:startDay )  " +
                "                       OR (a.refund_date <= :endDay   AND a.refund_date >=:startDay ))   " +
                "                       and 1=1   " +
                "        )a  " +
                "    LEFT JOIN 报价精品详细表 b   " +
                "    ON a.精品 = b.编号  " +
                "    JOIN lsh精品信息 c  " +
                "    ON b.id = c.精品编号   " +
                "    LEFT JOIN lsh精品分类 d  " +
                "    ON c.分类编号 = d.分类编号  " +
                "    LEFT JOIN lsh精品种类 e  " +
                "    ON d.种类编号 = e.种类编号  " +
                "   GROUP BY   " +
                "           品牌,产品种类 ,采购来源 " +
                "   UNION all  " +
                "       select   " +
                "           permit,  " +
                "           '' 产品种类,  " +
                "           '' 采购来源 ,  " +
                "           0 采购成本,  " +
                "           0 营业额,  " +
                "           0 利润,  " +
                "           DATE_FORMAT(valid_start_date,'%m') date,  " +
                "           sum(after_market_product_cost_buy_adjust) 成本调整,  " +
                "           b.品牌  " +
                "       FROM  " +
                "       INCOME_ADJUSTMENT a,  " +
                "       lsh库存表 c,  " +
                "       lsh车型 b  " +
                "   where   a.type = 1  " +
                "           and a.STOCK_CODE = c.库存编号  " +
                "           and b.车型编号 = c.车型编号  " +
                "           and DATE_FORMAT(valid_start_date,'%Y') = :year   " +
                "           and DATE_FORMAT(valid_start_date,'%m')>=DATE_FORMAT(:startDay,'%m')  " +
                "           and DATE_FORMAT(valid_start_date,'%m')<=DATE_FORMAT(:endDay,'%m')  " +
                "           and 1=1  " +
                "   GROUP BY b.品牌  " +
                "   )a  " +
                "   ON a.permit=allset.许可_BMBS  " +
                "   where 2=2  ";
        Map map = Maps.newHashMap();

        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql = sql.replace("2=2", " allset.大区= :permit  or  allset.大区_EN=:permit or " +
                    "   allset.CTA=:permit or allset.CTA_EN=:permit  or allset.许可_BMBS=:permit");
            map.put("permit", permit);
        }
        map.put("startDay",info.get("startDay"));
        map.put("endDay",info.get("endDay"));
        map.put("year",info.get("selectYear"));
        return this.getList(sql,map);
    }

}
